MySQL配置以及遇到的问题

数据库连接路径

JDBC 连接数据库

jdbc:mysql://localhost:3306/数据库名

# 设置编码
jdbc:mysql://127.0.0.1:3306/user?useUnicode=true&characterEncoding=utf8

# 携带上时区
jdbc:mysql://localhost:3306/testuser?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC

# 启用编译功能和允许 SSL 连接
jdbc:mysql://localhost:3306/my_fix_system?useUnicode=true&characterEncoding=utf8&useSSL=true&useServerPrepStmts=true

同时配置 useUnicode=true&characterEncoding=UTF8 作用有如下两个方面

1）存数据时数据库在存放项目数据的时候会先用UTF8格式将数据解码成字节码，然后再将解码后的字节码重新使用GBK编码存放到数据库中。

2）取数据时在从数据库中取数据的时候，数据库会先将数据库中的数据按GBK格式解码成字节码，然后再将解码后的字节码重新按UTF8格式编码据，最后再将数据返回给客户端。

常用的连接选项

参数名称	参数说明	缺省值	最低版本要求
user	数据库用户名（用于连接数据库）		所有版本
password	用户密码（用于连接数据库）		所有版本
useUnicode	是否使用Unicode字符集，如果参数characterEncoding设置为gb2312或gbk，本参数值必须设置为true	false	1.1g
characterEncoding	当useUnicode设置为true时，指定字符编码。比如可设置为gb2312或gbk	false	1.1g
autoReconnect	当数据库连接异常中断时，是否自动重新连接？	false	1.1
autoReconnectForPools	是否使用针对数据库连接池的重连策略	false	3.1.3
failOverReadOnly	自动重连成功后，连接是否设置为只读？	true	3.0.12
maxReconnects	autoReconnect设置为true时，重试连接的次数	3	1.1
initialTimeout	autoReconnect设置为true时，两次重连之间的时间间隔，单位：秒	2	1.1
connectTimeout	和数据库服务器建立socket连接时的超时，单位：毫秒。 0表示永不超时，适用于JDK 1.4及更高版本	0	3.0.1
socketTimeout	socket操作（读写）超时，单位：毫秒。 0表示永不超时	0	3.0.1

配置文件

[client]
port=3306
default-character-set=utf8
[mysqld]
# 设置mySQL的安装目录
basedir=D:\mysql5.7.28
# 设置mySQL的数据存放路径
datadir=D:\mysql5.7.28\data
# 设置端口号
port=3306
# 设置服务端使用的字符集为utf-8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 设置语法格式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 开启查询缓存
explicit_defaults_for_timestamp=true
# 跳过授权表,就是免登陆
#skip-grant-tables
# 输出错误log
log-error   = error.log
# 设置默认时区
default-time_zone = '+8:00'
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

注意：修改配置文件后需要重启数据库，如果初始化失败，改正错误后要把data文件夹里面的数据全部删除，才能重新初始化，它不会自动覆盖（数据保护机制）

配置文件的读取顺序

MYSQL 有多个配置文件，数据库是按

/etc/my.cnf
v
/etc/mysql/my.cnf
v
usr/local/mysql/etc/my.cnf
v
./my.cnf

从上到下的顺序读取配置文件的，那都有同一个配置项，按照哪个配置文件为准呢？

答：MySQL 会以最后读取到那个配置文件为准。

在Linux环境下，配置文件一般放在 /etc/my.cnf 下。在 Windows 平台下，配置文件的后缀名可能是 .cnf，也可能是 .ini

在 Win10 手动启动服务

1、通过运行窗口（Win+R）输入：services.msc 可打开服务列表里面可用找到正在运行的数据库服务，如果设置手动打开/关闭需要在这里操作

2、或者直接在命令行启动，例：net start mysql or net stop mysql

查看数据库信息

# 注意-P 和 -p 是有大小写之分
mysql -h localhost -P 3306 -u root -p root
# 如果是本机（且端口号为3306）可以直接：
mysql -u root -p

# 退出
exit # 或者 ctrl+C

进入数据库后，可以通过以下命令查看信息

查看全部库：SHOW databases
进入某个库（下次写表名时可以省的写数据库名）：USE 库名;
查看另一个库的所有表：show tables form 库名;
查看数据库版本 select VERSION();
查看当前所在数据库 select database();

默认有四个库

information_schema：存mySQL服务器维护的所有其他数据库信息
mysql：存用户信息
performance_schema：存性能参数
sys

MySQL 系统变量

参考资料 MySQL 系统变量(system variables)

MySQL 系统变量用于控制数据库的一些行为和方式的参数。比如启动数据库的时候设定多大的内存，使用什么样的隔离级别，日志文件的大小，存放位置等等一系列的东西。

系统变量取值：就是默认值，可以在启动时及启动后修改。
设置范围：全局与会话级别，全局级别需要 super 权限，会话级别只影响自身会话。
设置方法：启动前可以通过配置文件以及启动选项来修改，启动后通过 SET 子句来设置。
生效周期：全局变量全局可见，但只影响在更改后连接的从该全局变量初始化相应会话变量的客户端。当前会话及已登录的会话不受影响。

对于有关涉及到 size 的设置值，可以使用后缀 K、M 或 G 分别表示千字节、兆字节或 gigabytes，不区分大小写。

要想设置一个变量的值，使用下面的语法：

-- GLOBAL变量
SET GLOBAL sort_buffer_size=value;
-- 或
SET @@global.sort_buffer_size=value;


-- SESSION变量
SET SESSION sort_buffer_size=value;
-- 或
SET @@session.sort_buffer_size=value;
-- 或
SET sort_buffer_size=value;

常用操作

-- 显示系统版本
show variables like 'version%';
-- 查看mysql是否启用了查询日志
show global variables like "%genera%"
-- 打开日志
set global general_log = on
-- 关闭日志
set global general_log = off

--查看全部系统变量
show variables;


--恢复到缺省值
set 变量名=default;

日志系统

参考资料 Mysql 日志

用来记录 MySQL 的日常日志，包括查询、修改、更新等的每条 sql。

-- 查看mysql是否启用了查询日志
show global variables like "%genera%"

-- 打开日志
set global general_log = on
-- 关闭日志
set global general_log = off

MySQL 的错误日志默认 hostname.err 存放在 Mysql 的日志目录，可以通过以下语句查看：

-- 可以找到这个错误日志存放在哪里
show variables like "log_error";

-- 修改错误日志的位置
log_error = /tmp/mysql.log

注意：如果打开了日志功能，但是没有写入日志，那就有可能是mysql对日志文件的权限不够，所以需要指定权限

chown mysql:mysql /tmp/mysql_query.log

数据库备份

为什么要备份

保证重要的数据不丢失
数据转移

MySQL数据库备份的方式

直接拷贝物理文件的方式(data文件夹里)
通过 ide 导出表
使用自带的备份工具 mysqldump

# 从命令行导出一个表（不带表名就是直接导出一个数据库）
mysqldump -h localhost -u root -p 1234 库名 表名 > D:/a.sql

# 如果登陆mysql后想要导入前面导出的表/库
source D:/a.sql
# 或者直接
mysqldump -h localhost -u root -p 1234 库名 表名 < D:/a.sql

数据库无法远程连接

参考资料 MySQL Error: : 'Access denied for user 'root'@'localhost'

1、改表法。可能是你的帐号不允许从远程登陆，只能在 localhost。这个时候只要在 localhost 的那台电脑，登入 mysql 后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从 "localhost" 改称 "%"

use mysql;
update user set host = '%' where user = 'root';
select host, user from user;

最后，别忘了重启 mysql 使配置生效。

sudo systemctl restart mysql

2、授权法。例如，你想 user 使用 password 从任何主机连接到 mysql 服务器的话。（但是这个方法好像新版不行）

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
-- 如果是 root
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
-- 刷新访问权限表（不要忘了）
FLUSH PRIVILEGES;

SELECT user, host FROM user;

3、找到 "/etc/mysql/mysql.conf.d/mysqld.cnf" 文件并打开，加上如下配置

可以通过这个命令检查：

show variables like '%bind%';

mysql> show variables like '%bind%';
+---------------------+---------+
| Variable_name       | Value   |
+---------------------+---------+
| bind_address        | 0.0.0.0 |
| mysqlx_bind_address | 0.0.0.0 |
+---------------------+---------+
2 rows in set (0.00 sec)

最后，别忘了重启 mysql 使配置生效。

sudo systemctl restart mysql

4、需要将 root 的 plugin 属性修改为 mysql_native_password

USE mysql;
-- UPDATE user SET plugin='mysql_native_password' WHERE User='root';
update user set plugin='mysql_native_password';
FLUSH PRIVILEGES;

-- ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
-- ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';


exit;

重启

sudo systemctl restart mysql

5、最终解决办法：

找到 "/etc/mysql/mysql.conf.d/mysqld.cnf" 文件并打开，加上如下配置

skip-grant-tables

重启

sudo systemctl restart mysql

登录mysql，键入mysql –uroot –p；直接回车（Enter）

数据库连接路径​

常用的连接选项​

配置文件​

配置文件的读取顺序​

在 Win10 手动启动服务​

查看数据库信息​

MySQL 系统变量​

日志系统​

数据库备份​

数据库无法远程连接​